﻿using Microsoft.VisualStudio.Modeling;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Project.Co_DataTransformationPrototype.Data
{
    internal static class ScriptUtilities
    {
        internal static void generateConstraintScriptFromModel(IList<EntityShape> entities)
        {
            string fullpath;
            string sqlPKText;
            string strReferenceAttributeName;
            StringBuilder strbFKHolder = new StringBuilder();
            IList<EntityAttribute> listAttributes = new List<EntityAttribute>();
            string databaseName = Microsoft.VisualBasic.Interaction.InputBox("In which database are the tables located?", "Script Generation", "InitialDbase_M1");
            string strSQLUseStatement = "USE [" + databaseName + "] " + Environment.NewLine + "GO " + Environment.NewLine + Environment.NewLine;
            StringBuilder sql = new StringBuilder(strSQLUseStatement);

            System.Windows.Forms.FolderBrowserDialog fldDiag = new System.Windows.Forms.FolderBrowserDialog();
            fldDiag.Description = "Select the folder in which to store the new script:";
            if (fldDiag.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                fullpath = fldDiag.SelectedPath;
            else
                fullpath = @"C:\temp\";
            fullpath += @"\alter_table.sql";

            foreach (EntityShape shape in entities)
            {
                if (shape != null)
                {
                    // Reset
                    sqlPKText = string.Empty;
                    strbFKHolder.Clear();
                    listAttributes.Clear();
                    strReferenceAttributeName = string.Empty;
                    Entity ent = shape.ModelElement as Entity;

                    foreach (EntityAttribute attribute in ent.EntityAttributes)
                    {
                        if (attribute.isPrimaryKey)
                        {
                            listAttributes.Add(attribute);
                        }
                        else if (attribute.isForeignKey)
                        {                            
                            Entity referencedEntity = attribute.Store.ElementDirectory.FindElement(attribute.ReferencedEntityID) as Entity;
                            strbFKHolder.AppendFormat(@"ALTER TABLE [{0}].{1}" + Environment.NewLine, DatabaseSettings.SHEMA_NAME, ent.CurrentName);
                            strbFKHolder.AppendFormat(@"    ADD CONSTRAINT FK_{0}_{1}" + Environment.NewLine, ent.CurrentName, referencedEntity.CurrentName);
                            strbFKHolder.AppendFormat(@"    FOREIGN KEY ({0})" + Environment.NewLine, attribute.CurrentName);
                            strReferenceAttributeName = MappingJoinCondition.findMatchingIDAttributes(referencedEntity, ent).FirstOrDefault();
                            strbFKHolder.AppendFormat(@"    REFERENCES {0} ({1})" + Environment.NewLine, referencedEntity.CurrentName, strReferenceAttributeName);
                            strbFKHolder.AppendLine("GO" + Environment.NewLine);
                        }
                        else
                        {
                            continue;
                        }
                    }
                    foreach (EntityAttribute primaryKey in listAttributes)
                        sqlPKText += primaryKey.CurrentName + ", ";
                    
                    // Add primary key(s) constraint
                    sql.AppendFormat(@"ALTER TABLE [{0}].{1}" + Environment.NewLine, DatabaseSettings.SHEMA_NAME, ent.CurrentName);
                    sql.AppendFormat(@"    ADD CONSTRAINT PK_{0}" + Environment.NewLine, ent.CurrentName);
                    sql.AppendFormat(@"    PRIMARY KEY ({0});" + Environment.NewLine, sqlPKText.Substring(0,sqlPKText.Length-2));
                    sql.AppendLine("GO" + Environment.NewLine);

                    // Add foreign key(s) constraint
                    sql.Append(strbFKHolder.ToString());
                }
            }

            if (!string.Equals(strSQLUseStatement, sql.ToString()))
            {
                System.IO.File.WriteAllText(fullpath, sql.ToString());
                Microsoft.VisualBasic.Interaction.MsgBox("Script was created succesfully", Microsoft.VisualBasic.MsgBoxStyle.Information, "Script Generation");
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="docView"></param>
        internal static void generateDatabaseScriptFromModelElements(IList<EntityShape> entities)
        {
            if (entities.Count > 0)
            {
                string fullpath;
                string strSQLUseStatement = "USE [Avanade_MSCRM] " + Environment.NewLine + "GO " + Environment.NewLine;
                System.Windows.Forms.FolderBrowserDialog fldDiag = new System.Windows.Forms.FolderBrowserDialog();

                if (fldDiag.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                    fullpath = fldDiag.SelectedPath;
                else
                    fullpath = @"C:\temp\";
                fullpath += @"\create_tables.sql";

                StringBuilder sql = new StringBuilder(strSQLUseStatement);

                foreach (EntityShape shape in entities.OfType<EntityShape>())
                {
                    if (shape != null)
                    {
                        Entity ent = shape.ModelElement as Entity;
                        sql.AppendFormat(@"IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[{0}]') AND type in (N'U'))" + Environment.NewLine
                            , "new_" + ent.CurrentName);
                        sql.AppendFormat("    select * into [dbo].[{0}] from [dbo].[{1}] " + Environment.NewLine, "new_" + ent.CurrentName, ent.CurrentName);
                        sql.AppendLine("GO" + Environment.NewLine);
                    }
                }

                if (!string.Equals(strSQLUseStatement, sql.ToString()))
                    System.IO.File.WriteAllText(fullpath, sql.ToString());
            }
        }
    }
}
